package com.ffms.dao.impl;

import com.ffms.dao.ShouruDao;
import com.ffms.entity.Admin;
import com.ffms.entity.FamilyMember;
import com.ffms.entity.Shouru;
import com.ffms.entity.ZhiChu;
import com.ffms.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 从卿
 * @create 2022/3/7-23:42
 */
public class ShouruDaoImpl implements ShouruDao {

    @Override
    public List<Shouru> shouru() {
        List<Shouru> shouruList = new ArrayList<Shouru>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select tm.*,sh.* from tables.t_shouru sh join tables.t_member tm on tm.sid = sh.mssid where 1=1";
            //3.获取执行sql的对象)
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {

                int shouruid = rs.getInt("shouruid");
                String from1 = rs.getString("from");
                String money = rs.getString("money");
                Date date = rs.getDate("date");
                String way = rs.getString("way");
                int mssid = rs.getInt("mssid");
                String name = rs.getString("name");
                int sid = rs.getInt("sid");
                FamilyMember fm = new FamilyMember(sid, name);
                Shouru shouru = new Shouru(shouruid, from1, money, date, way, fm);
//                将对象放入集合
                shouruList.add(shouru);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return shouruList;
    }

    @Override
    public Shouru getById(Integer id) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Shouru shouru = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select tm.*,sh.* from tables.t_shouru sh join tables.t_member tm on tm.sid = sh.mssid where  shouruid =" + id;
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                int shouruid = rs.getInt("shouruid");
                String from1 = rs.getString("from");
                String money = rs.getString("money");
                Date date = rs.getDate("date");
                String way = rs.getString("way");
                int mssid = rs.getInt("mssid");
                String name = rs.getString("name");
                int sid = rs.getInt("sid");
                FamilyMember fm = new FamilyMember(sid, name);
                shouru = new Shouru(shouruid, from1, money, date, way, fm);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return shouru;
    }

    @Override
    public List<Shouru> shouruByMid(int thisSid) {
        List<Shouru> shouruList = new ArrayList<Shouru>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select tm.*,sh.* from tables.t_shouru sh join tables.t_member tm on tm.sid = sh.mssid where sh.mssid=" + thisSid;
            //3.获取执行sql的对象)
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {

                int shouruid = rs.getInt("shouruid");
                String from1 = rs.getString("from");
                String money = rs.getString("money");
                Date date = rs.getDate("date");
                String way = rs.getString("way");
                int mssid = rs.getInt("mssid");
                String name = rs.getString("name");
                int sid = rs.getInt("sid");
                FamilyMember fm = new FamilyMember(sid, name);
                Shouru shouru = new Shouru(shouruid, from1, money, date, way, fm);
//                将对象放入集合
                shouruList.add(shouru);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return shouruList;
    }

    @Override
    public List<Shouru> tj() {

        List<Shouru> shouruList = new ArrayList<Shouru>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select mssid, sum(money) a from t_shouru group by t_shouru.mssid";
            //3.获取执行sql的对象)
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int mssid = rs.getInt("mssid");
                String money = rs.getString("a");
                Shouru shouru = new Shouru(mssid, money);
//                将对象放入集合
                shouruList.add(shouru);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return shouruList;
    }



    @Override
    public int addShouru(Shouru shouru) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "insert into tables.t_shouru values (shouruid,?,?,?,?,?)";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);

            Object[] prams = {shouru.getFrom(), shouru.getMoney(),shouru.getDate(),shouru.getWay(),shouru.getMssid()};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);

            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return n;
    }


    public int updateShouru(Shouru shouru) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "update t_shouru set `from` =? ,money = ?, way = ? where shouruid=? ";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            Object[] prams = {shouru.getFrom(), shouru.getMoney(), shouru.getWay(), shouru.getId()};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);
            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return n;

    }

    public void deleteShouruById(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "delete from t_shouru where shouruid=?";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            Object[] prams = {id};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);
            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }
    @Override
    public Shouru findShouruId(int id) {
        Shouru shouru= null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select * from tables.t_member where sid  = '" + id + "' ";
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
               shouru= new Shouru(id);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return shouru;

    }
}
